import pymysql
import logging
import os
import random
from contextlib import contextmanager

from DBUtils.PooledDB import PooledDB


class DbManager(object):

    def __init__(self):
        # host = '0.0.0.0'
        host = '47.107.112.135'
        uname = 'sc'
        passwd = 'sc'
        db_name = 'carbon_metaverse'
        port = 3306
        self._pool = PooledDB(pymysql, mincached=1, maxconnections=0, host=host,
                              user=uname, passwd=passwd, db=db_name, port=port)

    def get_db_instance(self):
        return self._pool.connection()

    def UpdateOrDeleteOrInsert(self, sql, param=None):
        """ """
        res = True
        connection = self.get_db_instance()
        cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            if param == None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, param)
            connection.commit()
        except Exception as e:
            connection.rollback()
            logging.debug(repr(e))
            res = False
        cursor.close()
        connection.close()
        return res

    def queryOne(self, sql, param=None):
        """"""
        connection = self.get_db_instance()
        cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

        try:
            if param == None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, param)
            res = cursor.fetchone()
        except Exception as e:
            logging.debug(repr(e))
            res = None

        cursor.close()
        connection.close()
        return res

    def queryAll(self, sql, param=None):
        """"""
        connection = self.get_db_instance()
        cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

        try:
            if param == None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, param)
            res = cursor.fetchall()
        except Exception as e:
            logging.debug(repr(e))
            print(repr(e))
            res = None

        cursor.close()
        connection.close()
        return res

    def __enter__(self):
        try:
            connection = self.get_db_instance()
            cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
            return cursor
        finally:
            connection.commit()
            connection.close()
            print("success")

    def __exit__(self, exc_type, exc_val, exc_tb):
        return None


db_connect = DbManager()


def get_db_instance():
    return db_connect.get_db_instance()


if __name__ == "  dao  ":
    db_connect = DbManager()
    sql = 'select count(*) from sc_tenant'
    # test case1
    res = db_connect.queryAll('select count(*) from sc_tenant')
    print(str(res))
    # test case2
    res = db_connect.queryOne('select * from sc_tenant limit 1')
    print(str(res))
    # test case3
    with db_connect as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
        print(str(res))




